Left and Right Joins
This lesson discusses left and right joins.
We'll cover the following
Right Join#
Syntax for Left Join#
SELECT *
FROM table1
LEFT [OUTER] JOIN table2
ON <join condition>
Syntax for Right Join#
SELECT *
FROM table1
RIGHT [OUTER] JOIN table2
ON <join condition>
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/28lesson.sh and wait for the MySQL prompt to start-up.
-
We’ll start with the query from the inner join lesson that output all the actors with digital assets. If you remember, the inner join query only outputs celebrities who have a digital presence. If we use the LEFT JOIN instead, we’ll get a list of all the actors with or without digital presence. The query is shown below:
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
LEFT JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorID;
Note that the output now includes those actors who don’t have a digital presence. The LEFT JOIN includes those rows from the table on its left that don’t match with rows in the table to its right.
-
Interestingly, if we flip the order of the two tables in the query we get a different result:
SELECT FirstName, SecondName, AssetType, URL
FROM DigitalAssets
LEFT JOIN Actors
ON Actors.Id = DigitalAssets.ActorID;
The outcome makes sense, because the DigitalAssets table doesn’t have any rows that don’t have an owner in the Actors table, so all the rows in the DigitalAssets table match with a row in the Actors table and become part of the output. Note that actors without digital presence are left out.
-
The RIGHT JOIN is very similar to the LEFT JOIN. The only difference is that in the case of the left join, the unmatched rows come from the table specified on the left of the LEFT JOIN clause whereas, in the case of right join, the unmatched rows come from the table specified on the right of the RIGHT JOIN clause. If we use right join in the first query of the lesson, we would not need to flip the tables as we did above.
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
RIGHT JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorID;
- that an alternative syntax for left and right joins is LEFT OUTER JOIN and RIGHT OUTER JOIN respectively, though there’s no difference in functionality if you skip the OUTER keyword.
